﻿using System;
using System.Collections.Generic;

/// <summary>
/// Summary description for OutputXml
/// </summary>
public class OutputDbTable
{
    static public void RunExecutedProjectToSql( Data.MyListString response, 
        Data.Data analysisProject )
    {
        for( int currentVisiblePageIndex = 0,  visiblePageCount = analysisProject.GetPageCount() ;
                currentVisiblePageIndex < visiblePageCount ;
                currentVisiblePageIndex++ )
        {
            analysisProject.SetSelectedPage( currentVisiblePageIndex );
            Data.Page currentPage = analysisProject.GetSelectedPage();
            if( currentPage.Visible() )
            {
                String genericHeader = currentPage.GetParameter("[sql_generic_header]");
                response.Add(genericHeader);

                String sql_want_db_created = currentPage.GetParameter("[sql_want_db_created]");
                if (sql_want_db_created.Length > 0 
                    && sql_want_db_created.StartsWith( "F", StringComparison.CurrentCultureIgnoreCase ) == false )
                    CreateDatabase(response, currentPage);

                String sql_want_table_created = currentPage.GetParameter("[sql_want_table_created]");
                if (sql_want_table_created.Length > 0 
                    && sql_want_table_created.StartsWith( "F", StringComparison.CurrentCultureIgnoreCase ) == false )
                    CreateTable(response, currentPage);

                GenTableData(response, currentPage);

                String genericFooter = currentPage.GetParameter("[sql_generic_footer]");
                response.Add(genericFooter);
            }
        }
    }

    static private void CreateDatabase(Data.MyListString response, Data.Page currentPage)
    {
        String schemaCreationVerbage = currentPage.GetParameter("[sql_create_database]");
        response.Add(schemaCreationVerbage);
    }

    static private void CreateTable(Data.MyListString response, Data.Page currentPage)
    {
        String tableCreationVerbage = currentPage.GetParameter("[sql_create_entire_command]");
        if (tableCreationVerbage.Length == 0)
        {
            String stringSpec = currentPage.GetParameter("[sql_create_string_spec]");
            if (stringSpec.Length == 0)
                stringSpec = " VARCHAR(45) NOT NULL ";
            String doubleSpec = currentPage.GetParameter("[sql_create_double_spec]");
            if (doubleSpec.Length == 0)
                doubleSpec = " FLOAT ";

            String tableName = currentPage.GetParameter("[sql_tablename]");
            // create our own table based on what we know...
            response.Add("CREATE TABLE " + tableName + " ( ");

            String thisRecord = currentPage.GetRowLabelName() + stringSpec;
            for (int textIndex = 0, textCount = currentPage.GetTextColumnCount(); textIndex < textCount; textIndex++)
            {
                thisRecord += "," + currentPage.GetTextColumnName(textIndex) + stringSpec ;
            }
            for (int numericIndex = 0, numericCount = currentPage.GetNumericColumnCount(); numericIndex < numericCount; numericIndex++)
            {
                thisRecord += "," + currentPage.GetNumericColumnName(numericIndex) + doubleSpec ;
            }
            response.Add( thisRecord );
            response.Add(" ) ");
            response.Add(currentPage.GetParameter("[sql_createsuffix]")); // "ENGINE=InnoDB DEFAULT CHARSET=utf8"  // n.b. MySQL 

        }
        else
            response.Add(tableCreationVerbage);
    }

    static private void GenTableData(Data.MyListString response, Data.Page currentPage)
    {
        String tableUseVerbage = currentPage.GetParameter("[sql_use_dbname]");
        /*  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
            SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
            SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
            USE databaseName; 
         
            // n.b. if you want the tabled emptied, remember you must put
            // the appopriate syntax here
         
         */

        String insertPrefix = currentPage.GetParameter("[sql_insert_prefix]");
        /* SET AUTOCOMMIT=0; INSERT INTO <tablename> VALUES 
         */
        
        response.Add(tableUseVerbage); 
        response.Add(insertPrefix);

        int rowCount = currentPage.GetRowCount();
        for( int rowIndex = 0 ; rowIndex < rowCount ; rowIndex++ )
        {
            String thisRecord = "";

            if (rowIndex > 0)
                thisRecord += ",";

            thisRecord +=  " ( " ;

            thisRecord +=  rowIndex.ToString() +  "," ;

            thisRecord += "'" + currentPage.GetRowLabelValue(rowIndex) + "'" + ",";

            for( int textColumnIndex = 0, textCount = currentPage.GetTextColumnCount();
                    textColumnIndex < textCount ; textColumnIndex++ )
            {
                thisRecord += "'" + currentPage.GetTextColumnValue(rowIndex, textColumnIndex) + "'" + "," ;
            }
            for( int numericColumnIndex = 0, columnCount = currentPage.GetNumericColumnCount();
                    numericColumnIndex < columnCount ; numericColumnIndex++ )
            {
                // n.b. the following adds the formatted value.
                thisRecord += currentPage.GetNumericCellContents(rowIndex, numericColumnIndex, true ) + ",";
            }
            response.Add( thisRecord + " )");
        }

        String insertSuffix = currentPage.GetParameter("[sql_insert_suffix]");
        /* COMMIT; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 
         */
        response.Add(insertSuffix);

    }
}
